#!/usr/bin/python
# -*- coding: UTF-8 -*-

#import MySQLdb
import time
import datetime
import mysql.connector

# 创建链接数据库
config = {'host': '127.0.0.1',  # 默认127.0.0.1
          'user': 'root', 'password': '654321', 'port': 3306,  # 默认即为3306
          'database': 'worm_data', 'charset': 'utf8'  # 默认即为utf8
          }

config1 = {'host': 'localhost',  # 默认127.0.0.1
          'user': 'root', 'password': '123456', 'port': 3306,  # 默认即为3306
          'database': 'shopxxb2b2c', 'charset': 'utf8'  # 默认即为utf8
          }

# 新正式服务器
config2 = {'host': 'rm-2zefd6473rz062234.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'prod_ccii', 'password': 'cI1546_wodesecerts', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }

config3 = {'host': 'rm-2zefd6473rz062234.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'prod_python', 'password': '939_58J6kAW)P&^', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }


# 保存到数据库
def save(data):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config3)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        # 第三种：可以一次插入多条，效率比一条条插高,用的方法是executemany 猪牛羊 1 2 3
        stmt = 'insert into shop_price_daily_info(name, price, province_name, market_name, price_date, type, created_date, last_modified_date, version, copy_data) values (%s, %s, %s, %s, %s, %s, NOW(), NOW(), 0, 1)'
        # for item in data:
        #     print(item)
        # cursor.execute(stmt, item)
        cursor.executemany(stmt, data)
        cnn.commit()
        print("插入数据成功！")

    except mysql.connector.Error as e:
        print('插入数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接


# 查询数据库列表
def findList(type, price_date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config3)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = 'select name, price, province_name, market_name, price_date, type from shop_price_daily_info where type = %s and price_date = %s order by id desc '
        cursor.execute(stmt, (type, price_date))
        data = cursor.fetchall()
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    #print(re_date)
    return re_date

def work(type, dayNum):
    # 前1天数据 基准数据
    data_1 = findList(type, getdate(dayNum))
    data_1_copy = []
    data_1_copy.extend(data_1)

    list_data1 = list(set(data_1))
    list_data1_copy = list(set(data_1))

    market_name1 = []
    market_name1_copy = []
    # 获取市场名称
    for i, item in enumerate(list_data1):
        market_name1.append(item[3])

    market_name1_copy.extend(market_name1)

    # 昨天数据
    data_2 = findList(type,  getdate(dayNum - 1))
    list_data2 = list(set(data_2))
    market_name2 = []
    # 获取市场名称
    for i, item in enumerate(list_data2):
        market_name2.append(item[3])

    # 获得B中没有A的A中的数据
    for i in market_name1:
        # 检查A中元素是否在B中
        if i in market_name2:
            market_name1_copy.remove(i)

    print('昨天缺少的数据条数',len(market_name1_copy))

    for i,item in enumerate(list_data1):
        if item[3] not in market_name1_copy:
            list_data1_copy.remove(item)

    # 最终要保存的数据
    result_data = []
    todayDate = getdate(dayNum - 1)
    for i,item in enumerate(list_data1_copy):
        # ('猪肉(白条猪)', 25.6, '浙江省', '浙江嘉善浙北果蔬菜批发交易', '2019-08-03', 1)
        arr = list(item)
        arr[4] = str(todayDate)
        result_data.append(tuple(arr))

    print(result_data)

    if len(result_data) > 0:
        save(result_data)

    print(type,"类型数据处理完成！")


pork_type = 1
beef_type = 2
mutton_type = 3
fish_type = 4001

if __name__ == '__main__':
    # num = 2 是前天到昨天 每次只拷贝1天
    num = 2
    work(pork_type, num)
    work(beef_type, num)
    work(mutton_type, num)
    work(fish_type, num)

        
        
